﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace Org.Kaka.CsmsUtil.DataEI.Import
{
    public class ExcelHelper
    {
        string strConn;
        string sheetName;
        string filePuth;

        public string SheetName
        {
            get { return sheetName; }
            set { sheetName = value; }
        }
        public string StrConn
        {
            get
            {
                if (!string.IsNullOrEmpty(strConn))
                    return strConn;
                else if (!string.IsNullOrEmpty(filePuth))
                    return "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePuth + ";Extended Properties=Excel 8.0;";
                else
                    throw new Exception("Excel路径为空或在调用get时没有初始化！");
            }
            set { strConn = value; }
        }
        public string FilePuth
        {
            get { return filePuth; }
            set { filePuth = value; }
        }

        /// <summary>
        /// 通过OLDB方式读取Excel数据
        /// </summary>
        /// <param name="strConn">OLDB链接字符串</param>
        /// <param name="sheetName">要读取的Sheet名称</param>
        /// <returns>DataSet数据集</returns>
        public DataSet GetExcelDS(string strConn, string sheetName)
        {
            using (System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn))
            {

                conn.Open();
                string strExcel = "";
                System.Data.OleDb.OleDbDataAdapter myCommand = null;
                DataSet ds = null;
                strExcel = "select * from [" + sheetName + "]";
                myCommand = new System.Data.OleDb.OleDbDataAdapter(strExcel, conn);
                ds = new DataSet();
                myCommand.Fill(ds, "excelTable");
                return ds;

            }
        }
        public DataSet GetExcelDS()
        {
            if (string.IsNullOrEmpty(sheetName))
            {
                throw new Exception("在调用GetExcelDS没有显式的给SheetName负值");
            }
            return GetExcelDS(StrConn, SheetName);
        }

        /// <summary>
        /// 获取链接字符串
        /// </summary>
        /// <param name="excelVersion">Excel版本信息</param>
        /// <param name="excelPuth">Excel路径</param>
        /// <returns>Excel链接字符串</returns>
        public string ExcelConnString(Org.Kaka.CsmsUtil.DataEI.Base.ExcelVersion excelVersion, string excelPuth)
        {
            if (string.IsNullOrEmpty(excelPuth))
                throw new Exception("Excel路径为空！");
            StringBuilder excelConnString = new StringBuilder();
            switch (excelVersion)
            {
                case Org.Kaka.CsmsUtil.DataEI.Base.ExcelVersion.V2003:
                    excelConnString.Append("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=");
                    excelConnString.Append(excelPuth);
                    excelConnString.Append(";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'");
                    break;
                case Org.Kaka.CsmsUtil.DataEI.Base.ExcelVersion.V2007:
                    excelConnString.Append("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='");
                    excelConnString.Append(excelPuth);
                    excelConnString.Append("';Extended Properties='Excel 12.0;HDR=YES;IMEX=1'");
                    break;
                case Org.Kaka.CsmsUtil.DataEI.Base.ExcelVersion.V2010:
                    excelConnString.Append("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='");
                    excelConnString.Append(excelPuth);
                    excelConnString.Append("';Extended Properties='Excel 12.0;HDR=YES;IMEX=1'");
                    break;
                default:
                    throw new Exception("没有找到对应的Excel版本信息--" + excelVersion.ToString());
            }
            return excelConnString.ToString();
        }
    }
}
